package rpt.yto.flex.remote.data.dao.processor;

public class Query_allpsnchange {

	private static Query_allpsnchange query = new Query_allpsnchange();

	private Query_allpsnchange() {

	}

	public static Query_allpsnchange getInstance() {

		return query;
	}

	public String getQuerySQL(String period) {

		StringBuilder builder = new StringBuilder();

		builder.append(" select rownum rowno , temp.* from ( ");
		builder.append(" select unitname3 unittype , unitname statistictype , decode(isworkingunit , 'Y' , '是' , 'N' , '否') isworkingunit , sum(yeartotal) yeartotal , sum(s_person + dimission) s_person , sum(induty) induty , sum(transin) transin ,  ");
		builder.append(" sum(dimission) dimmission , sum(transout) transout , sum(s_person + induty + transin - transout) e_person , 0 bnczj , 0 byrzl , 0 lzl from (  ");
		// 年初
		builder.append(" select corplevel.unitname3 , bd_corp.unitname ,  count(1) yeartotal , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person , bd_corp.isworkingunit ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp   , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and ((bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and indutydate < substr('" + period + "' , 0 , 4) || '-00'  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')  ");
		builder.append(" group by bd_corp.unitname , corplevel.unitname3 , bd_corp.isworkingunit ");
		builder.append(" union all  ");
		// 月初
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , count(1) s_person , 0 induty , 0 dimission , 0 transin , 0 transout , 0 e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp      , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and ((bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"') and indutydate < '" + period + "'  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
		builder.append(" group by bd_corp.unitname  , corplevel.unitname3 , bd_corp.isworkingunit ");

		builder.append(" union all   ");

		// 本期入
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , 0 s_person , count(1) induty , 0 dimission , 0 transin , 0 transout , 0 e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp     , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_psndoc.pk_corp = bd_corp.pk_corp(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psnbasdoc.joinsysdate like '" + period + "' || '%'  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
		builder.append(" group by bd_corp.unitname  , corplevel.unitname3 , bd_corp.isworkingunit ");
		builder.append(" union all   ");

		// 本期离
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person , bd_corp.isworkingunit   ");
		builder.append(" from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp    , vw_yto_corp_level corplevel ");
		builder.append(" where hi_psndoc_dimission.pk_psndoc = bd_psndoc.pk_psndoc(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp  ");
		builder.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '" + period + "' || '%'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" group by  bd_corp.unitname  , corplevel.unitname3 , bd_corp.isworkingunit ");
		builder.append(" union all   ");
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , 0 s_person , 0 induty , count(1) dimission , 0 transin , 0 transout , 0 e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp    , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builder.append(" and bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psnbasdoc.joinsysdate like '" + period + "' || '%'  ");
		builder.append(" and bd_psndoc.outdutydate is not null   ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and bd_psndoc.pk_psndoc not in   ");
		builder.append(" ( select hi_psndoc_dimission.pk_psndoc  from hi_psndoc_dimission , bd_psndoc , om_job , bd_corp  ");
		builder.append(" where bd_psndoc.pk_psndoc(+) = hi_psndoc_dimission.pk_psndoc  ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_corp.pk_corp(+) = hi_psndoc_dimission.pk_corp  ");
		builder.append(" and hi_psndoc_dimission.poststat = 'N' and hi_psndoc_dimission.leavedate like '" + period + "' || '%'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" ) group by bd_corp.unitname   , corplevel.unitname3 , bd_corp.isworkingunit ");
		builder.append(" union all   "); 

		// 本期调入
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , 0 s_person , 0 induty , 0 dimission , count(1) transin , 0 transout , 0 e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp    , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builder.append(" and ((bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate like '"+period+"%') and bd_psnbasdoc.joinsysdate <> indutydate and indutydate like '" + period + "' || '%'  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
		builder.append(" group by bd_corp.unitname   , corplevel.unitname3 , bd_corp.isworkingunit ");
		builder.append(" union all   ");

		// 本期调出
//		builder.append(" select corplevel.unitname3 , hi_stapplyb_h.pk_corp , 0 yeartotal , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person  ");
//		builder.append(" from hi_stapplyb_h , bd_psndoc , om_job , bd_corp    , vw_yto_corp_level corplevel ");
//		builder.append(" where bd_psndoc.pk_psndoc(+) = hi_stapplyb_h.pk_psndoc  ");
//		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
//		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
//		builder.append(" and bd_corp.pk_corp(+) = hi_stapplyb_h.pk_corp  ");
//		builder.append(" and nvl(hi_stapplyb_h.isin , 'Y') = 'N' and hi_stapplyb_h.pk_aimcorp <> hi_stapplyb_h.pk_corp and hi_stapplyb_h.pk_psndoc is not null   ");
//		builder.append(" and hi_stapplyb_h.deffectdate like '" + period + "' || '%'  ");
//		builder.append("  and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~')  ");
//		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
//		builder.append(" group by hi_stapplyb_h.pk_corp   , corplevel.unitname3 ");
//		builder.append(" union all   ");
		
		builder.append(" select corplevel.unitname3 ,bd_corp.unitname , 0 yeartotal , 0 s_person , 0 induty , 0 dimission , 0 transin , count(1) transout , 0 e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc , bd_psnbasdoc , om_job , bd_corp  , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)   ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job   ");
		builder.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp   ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+)  ");
		builder.append(" and bd_psndoc.psnclscope = 4   ");
		builder.append(" and bd_psndoc.outdutydate like '" + period + "' || '%'   ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~')  and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'     ");
		builder.append(" group by corplevel.unitname3 , bd_corp.unitname , bd_corp.isworkingunit  ");
		builder.append(" union all   ");
		
		// 期末
		builder.append(" select corplevel.unitname3 , bd_corp.unitname , 0 yeartotal , 0 s_person , 0 induty , 0 dimission , 0 transin , 0 transout , count(1) e_person , bd_corp.isworkingunit  ");
		builder.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp     , vw_yto_corp_level corplevel ");
		builder.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+)  ");
		builder.append(" and bd_psndoc.pk_corp = corplevel.pk_corp(+) ");
		builder.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job  ");
		builder.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp  ");
		builder.append(" and ((bd_psndoc.psnclscope = 0  ");
		builder.append(" and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and indutydate <= '" + period + "~'  ");
		builder.append(" and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') and bd_corp.unitdistinction not in ('0001V21000000000PY5X','0001V21000000002HODR','0001V210000000030G9G') and bd_corp.pk_corp <> '1001'  ");
		builder.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%')   ");
		builder.append(" group by bd_corp.unitname  , corplevel.unitname3 , bd_corp.isworkingunit) group by unitname , unitname3 , isworkingunit order by unitname3) temp ");

		System.out.println(builder.toString());
		
		return builder.toString();

	}

}
